/****** Object:  View [dbo].[InformeStock]    Script Date: 11/16/2011 00:02:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[InformeStock]
AS
SELECT     TOP (100) PERCENT dbo.productos.id_producto, dbo.categorias_producto.descripcion AS Categoria, dbo.marcas.descripcion AS Marca, 
                      dbo.productos.descripcion AS Producto, SUM(dbo.movimientos_lineas.cantidad) AS [Stock Actual], dbo.productos.stock_min AS [Stock Minimo], 
                      dbo.productos.stock_max AS [Stock Maximo], MAX(dbo.movimientos.fecha_movimiento) AS [Ultimo Movimiento], dbo.productos.id_marca, 
                      dbo.productos.id_categoria
FROM         dbo.movimientos INNER JOIN
                      dbo.movimientos_lineas ON dbo.movimientos.id_mov = dbo.movimientos_lineas.id INNER JOIN
                      dbo.productos ON dbo.movimientos_lineas.id_producto = dbo.productos.id_producto INNER JOIN
                      dbo.marcas ON dbo.productos.id_marca = dbo.marcas.id_marca INNER JOIN
                      dbo.categorias_producto ON dbo.productos.id_categoria = dbo.categorias_producto.id_categoria
GROUP BY dbo.productos.id_producto, dbo.productos.descripcion, dbo.productos.stock_min, dbo.productos.stock_max, dbo.marcas.descripcion, 
                      dbo.categorias_producto.descripcion, dbo.productos.id_marca, dbo.productos.id_categoria
ORDER BY Categoria, Marca, Producto

GO

CREATE TABLE [dbo].[productos_auditoria](
	id_audit int NOT NULL PRIMARY KEY identity,
	[id_producto] [int] NOT NULL,
	[descripcion] [nvarchar](255) NULL,
	[id_marca] [int] NULL,
	[id_categoria] [int] NULL,
	[costo] [float] NULL,
	[ganancia] [float] NULL,
	[stock_min] [float] NULL,
	[stock_max] [float] NULL,
	tipo_auditoria nvarchar(10) NULL,
	fecha_modif datetime NOT NULL,
	id_usuario_modif int NULL FOREIGN KEY REFERENCES dbo.usuarios(id_usuario)
)
GO

ALTER TABLE dbo.productos ADD
	tipo_auditoria varchar(10) DEFAULT 'ALTA',
	fecha_modif datetime NOT NULL CONSTRAINT DF_reparaciones_fecha_modif DEFAULT getDate(),
	id_usuario_modif int NULL
GO 


CREATE TRIGGER ProductosOnUpdate 
ON productos FOR UPDATE AS
BEGIN 

SET NOCOUNT ON
INSERT INTO [dbo].[productos_auditoria]
           ([id_producto]
           ,[descripcion]
           ,[id_marca]
           ,[id_categoria]
           ,[costo]
           ,[ganancia]
           ,[stock_min]
           ,[stock_max]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif])
	SELECT  [id_producto]
           ,[descripcion]
           ,[id_marca]
           ,[id_categoria]
           ,[costo]
           ,[ganancia]
           ,[stock_min]
           ,[stock_max]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif]
	FROM deleted
  
	UPDATE [productos] SET 
		tipo_auditoria = 'MODI',
		fecha_modif = getDate(),
		id_usuario_modif = [dbo].get_current_user()
	WHERE id_producto IN (SELECT id_producto FROM Inserted)

SET NOCOUNT OFF
END

GO



CREATE TRIGGER ProductosOnDelete 
ON productos FOR DELETE AS
BEGIN 

SET NOCOUNT ON
INSERT INTO [dbo].[productos_auditoria]
           ([id_producto]
           ,[descripcion]
           ,[id_marca]
           ,[id_categoria]
           ,[costo]
           ,[ganancia]
           ,[stock_min]
           ,[stock_max]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif])
	SELECT  [id_producto]
           ,[descripcion]
           ,[id_marca]
           ,[id_categoria]
           ,[costo]
           ,[ganancia]
           ,[stock_min]
           ,[stock_max]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif]
	FROM deleted
  INSERT INTO [solinf].[dbo].[productos_auditoria]
           ([id_producto]
           ,[descripcion]
           ,[id_marca]
           ,[id_categoria]
           ,[costo]
           ,[ganancia]
           ,[stock_min]
           ,[stock_max]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif])
	SELECT  [id_producto]
           ,[descripcion]
           ,[id_marca]
           ,[id_categoria]
           ,[costo]
           ,[ganancia]
           ,[stock_min]
           ,[stock_max]
           ,'BAJA'
           ,getDate()
           ,[dbo].get_current_user()
	FROM deleted
SET NOCOUNT OFF
END

GO


CREATE VIEW [dbo].[RankingVendedores]
AS
SELECT     TOP (100) PERCENT dbo.usuarios.id_usuario, dbo.usuarios.apellido, dbo.usuarios.nombre, 
                      SUM(dbo.presupuestos_lineas.cantidad * dbo.presupuestos_lineas.precio_unitario) AS total
FROM         dbo.presupuestos INNER JOIN
                      dbo.presupuestos_lineas ON dbo.presupuestos.id_presupuesto = dbo.presupuestos_lineas.id INNER JOIN
                      dbo.usuarios ON dbo.presupuestos.id_vendedor = dbo.usuarios.id_usuario
WHERE     (dbo.presupuestos.estado = N'APROBADO') AND (DATEPART(month, GETDATE()) = DATEPART(month, dbo.presupuestos.fecha_alta)) AND (DATEPART(year, GETDATE()) 
                      = DATEPART(year, dbo.presupuestos.fecha_alta))
GROUP BY dbo.usuarios.nombre, dbo.usuarios.apellido, dbo.usuarios.id_usuario, dbo.presupuestos.estado
ORDER BY dbo.usuarios.apellido, dbo.usuarios.nombre

GO

CREATE VIEW [dbo].[RankingProductos]
AS
SELECT     dbo.productos.id_producto, dbo.productos.descripcion, SUM(dbo.presupuestos_lineas.cantidad) AS Expr1
FROM         dbo.presupuestos INNER JOIN
                      dbo.presupuestos_lineas ON dbo.presupuestos.id_presupuesto = dbo.presupuestos_lineas.id INNER JOIN
                      dbo.productos ON dbo.presupuestos_lineas.id_producto = dbo.productos.id_producto
WHERE     (dbo.presupuestos.estado = N'APROBADO') AND (DATEPART(month, GETDATE()) = DATEPART(month, dbo.presupuestos.fecha_alta)) AND (DATEPART(year, GETDATE()) 
                      = DATEPART(year, dbo.presupuestos.fecha_alta))
GROUP BY dbo.productos.id_producto, dbo.productos.descripcion

GO



CREATE VIEW [dbo].[RankingClientes]
AS
SELECT     TOP (100) PERCENT dbo.clientes.id_cliente, dbo.clientes.razon_social, dbo.clientes.nro_doc, dbo.clientes.tipo_doc, 
                      SUM(dbo.presupuestos_lineas.cantidad * dbo.presupuestos_lineas.precio_unitario) AS total
FROM         dbo.clientes INNER JOIN
                      dbo.presupuestos ON dbo.clientes.id_cliente = dbo.presupuestos.id_cliente INNER JOIN
                      dbo.presupuestos_lineas ON dbo.presupuestos.id_presupuesto = dbo.presupuestos_lineas.id
WHERE     (dbo.presupuestos.estado = N'APROBADO') AND (DATEPART(month, GETDATE()) = DATEPART(month, dbo.presupuestos.fecha_alta)) AND (DATEPART(year, GETDATE()) 
                      = DATEPART(year, dbo.presupuestos.fecha_alta))
GROUP BY dbo.clientes.id_cliente, dbo.clientes.razon_social, dbo.clientes.nro_doc, dbo.clientes.tipo_doc
ORDER BY total DESC

GO

CREATE VIEW [dbo].[ProductosStockMin]
AS
SELECT     dbo.productos.id_producto, dbo.productos.descripcion, dbo.marcas.descripcion AS marca, dbo.categorias_producto.descripcion AS categoria, 
                      dbo.productos.stock_min, dbo.productos.stock_max, SUM(dbo.movimientos_lineas.cantidad) AS stock
FROM         dbo.categorias_producto INNER JOIN
                      dbo.productos ON dbo.categorias_producto.id_categoria = dbo.productos.id_categoria INNER JOIN
                      dbo.marcas ON dbo.productos.id_marca = dbo.marcas.id_marca LEFT OUTER JOIN
                      dbo.movimientos_lineas ON dbo.productos.id_producto = dbo.movimientos_lineas.id_producto
GROUP BY dbo.productos.id_producto, dbo.productos.descripcion, dbo.marcas.descripcion, dbo.categorias_producto.descripcion, dbo.productos.stock_min, 
                      dbo.productos.stock_max
HAVING      (SUM(dbo.movimientos_lineas.cantidad) <= dbo.productos.stock_min)

GO


/** auditoria de presupuestos */


CREATE TABLE [dbo].[presupuestos_auditoria](
	id_audit int NOT NULL PRIMARY KEY identity,
	[id_presupuesto] [int] NOT NULL,
	[id_cliente] [int] NULL,
	[id_vendedor] [int] NULL,
	[fecha_alta] [datetime] NULL,
	[fecha_vencimiento] [datetime] NULL,
	[estado] [nvarchar](20) NULL,
	[forma_pago] [nvarchar](20) NULL,
	[comentatio] [nvarchar](255) NULL,
	tipo_auditoria nvarchar(10) NULL,
	fecha_modif datetime NOT NULL,
	id_usuario_modif int NULL FOREIGN KEY REFERENCES dbo.usuarios(id_usuario)
)
GO


ALTER TABLE dbo.presupuestos ADD
	tipo_auditoria varchar(10) DEFAULT 'ALTA',
	fecha_modif datetime NOT NULL CONSTRAINT DF_presupuestos_fecha_modif DEFAULT getDate(),
	id_usuario_modif int NULL
GO 


CREATE TRIGGER PresupuestosOnUpdate 
ON presupuestos FOR UPDATE AS
BEGIN 

SET NOCOUNT ON
INSERT INTO [dbo].[presupuestos_auditoria]
           ([id_presupuesto]
           ,[id_cliente]
           ,[id_vendedor]
           ,[fecha_alta]
           ,[fecha_vencimiento]
           ,[estado]
           ,[forma_pago]
           ,[comentatio]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif])
     SELECT [id_presupuesto]
           ,[id_cliente]
           ,[id_vendedor]
           ,[fecha_alta]
           ,[fecha_vencimiento]
           ,[estado]
           ,[forma_pago]
           ,[comentatio]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif]
	FROM deleted
  
	UPDATE [presupuestos] SET 
		tipo_auditoria = 'MODI',
		fecha_modif = getDate(),
		id_usuario_modif = [dbo].get_current_user()
	WHERE id_presupuesto IN (SELECT id_presupuesto FROM Inserted)

SET NOCOUNT OFF
END

GO

CREATE TRIGGER PresupuestosOnDelete 
ON presupuestos FOR DELETE AS
BEGIN 

SET NOCOUNT ON
INSERT INTO [dbo].[presupuestos_auditoria]
           ([id_presupuesto]
           ,[id_cliente]
           ,[id_vendedor]
           ,[fecha_alta]
           ,[fecha_vencimiento]
           ,[estado]
           ,[forma_pago]
           ,[comentatio]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif])
     SELECT [id_presupuesto]
           ,[id_cliente]
           ,[id_vendedor]
           ,[fecha_alta]
           ,[fecha_vencimiento]
           ,[estado]
           ,[forma_pago]
           ,[comentatio]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif]
	FROM deleted
	INSERT INTO [dbo].[presupuestos_auditoria]
           ([id_presupuesto]
           ,[id_cliente]
           ,[id_vendedor]
           ,[fecha_alta]
           ,[fecha_vencimiento]
           ,[estado]
           ,[forma_pago]
           ,[comentatio]
           ,[tipo_auditoria]
           ,[fecha_modif]
           ,[id_usuario_modif])
     SELECT [id_presupuesto]
           ,[id_cliente]
           ,[id_vendedor]
           ,[fecha_alta]
           ,[fecha_vencimiento]
           ,[estado]
           ,[forma_pago]
           ,[comentatio]
           ,'BAJA'
           ,getDate()
           ,[dbo].get_current_user()
	FROM deleted
SET NOCOUNT OFF
END

GO